<?php
class MPayroll extends CI_Model{

	public function __construct()
    {
        parent::__construct();
    }
	function getPayroll($id) {
		$data = array();
	    $this->db->where('payRollID',id_clean($id));
	    $this->db->limit(1);
	    $Q = $this->db->get('payroll');
	    if ($Q->num_rows() > 0){
	      $data = $Q->row_array();
	    }
	
	    $Q->free_result();    
	    return $data;      
	}
    
    function getPayrollDetailByPayrollId($id)
    {
         $data = array();
	     $this->db->select('payroll.*, staff.FirstName, staff.MiddleName, staff.LastName, staff.NickName, position.Title, team.TeamName');
	     $this->db->from('staff');
	     $this->db->join('position','position.PositionID = staff.PositionID','left');
	     $this->db->join('team', 'staff.TeamID = team.TeamID','left');
         $this->db->join('payroll','payroll.staffID = staff.StaffID');
         $this->db->where('payroll.payRollID',id_clean($id));
         $this->db->limit(1);
	     $Q = $this->db->get();
	     if ($Q->num_rows() > 0){
	       $data = $Q->row_array();
	     }
	     $Q->free_result();    
	     return $data; 	    
    }
    
    function getPayrollByStaffId($id, $datePaid = '')
    {
         $data = array();
	     $this->db->select('payroll.*, staff.FirstName, staff.MiddleName, staff.LastName, staff.NickName, position.Title, team.TeamName');
	     $this->db->from('staff');
	     $this->db->join('position','position.PositionID = staff.PositionID','left');
	     $this->db->join('team', 'staff.TeamID = team.TeamID','left');
         $this->db->join('payroll','payroll.staffID = staff.StaffID');
         $this->db->where('staff.StaffID',id_clean($id));
         if($datePaid != '')
         {
            $this->db->where('DATE(payroll.PayRollDate)', date('Y-m-d', strtotime($datePaid)));  
         }
	     $Q = $this->db->get();
	     if ($Q->num_rows() > 0){
            foreach ($Q->result_array() as $row){
	           $data[] = $row;
	        }
	     }
	     $Q->free_result();    
	     return $data; 	    
    }
    
	function getPayrollList($datePaid = '')
    {
         $data = array();
	     $this->db->select('payroll.*, staff.FirstName, staff.MiddleName, staff.LastName, staff.NickName, position.Title, team.TeamName');
	     $this->db->from('staff');
	     $this->db->join('position','position.PositionID = staff.PositionID','left');
	     $this->db->join('team', 'staff.TeamID = team.TeamID','left');
         $this->db->join('payroll','payroll.staffID = staff.StaffID');
         if($datePaid != '')
         {
            $this->db->where('DATE(payroll.PayRollDate)', date('Y-m-d', strtotime($datePaid)));  
         }
	     $this->db->order_by('payroll.CreatedDate', 'DESC');
	     $Q = $this->db->get();
	     if ($Q->num_rows() > 0){
	       foreach ($Q->result_array() as $row){
	         $data[] = $row;
	       }
	     }
	     $Q->free_result();    
	     return $data; 	 
    }
    
    function createPayroll()
    {
        $allAndAddition = id_clean($_POST['grossSalary']) + id_clean($_POST['HealthBenifit']) + id_clean($_POST['PhoneAllowance']) + id_clean($_POST['BankCharge']) + id_clean($_POST['AnnualLeave'])  + id_clean($_POST['AnnualBonus']) + id_clean($_POST['OverTime']);
        $allDeduction = id_clean($_POST['SalaryTax']) + id_clean($_POST['Other']) + id_clean($_POST['SalaryAdvance']);
        $netSalary =  $allAndAddition - $allDeduction;
        $data = array( 
            'staffID' => id_clean($_POST['StaffID']),
            'PayRollDate' => db_clean($_POST['PayRollDate']),
            'grossSalary' => db_clean($_POST['grossSalary']),
            'SalaryTax' => db_clean($_POST['SalaryTax']),
            'Other' => db_clean($_POST['Other']),
            'SalaryAdvance' => db_clean($_POST['SalaryAdvance']),
            'HealthBenifit' => db_clean($_POST['HealthBenifit']),
            'PhoneAllowance' => db_clean($_POST['PhoneAllowance']),
            'BankCharge' => db_clean($_POST['BankCharge']),
            'AnnualLeave' => db_clean($_POST['AnnualLeave']),
            'AnnualBonus' => db_clean($_POST['AnnualBonus']),
            'OverTime' => db_clean($_POST['OverTime']),
            'NetSalary' => $netSalary,
            'Notes' => db_clean($_POST['Notes'])
		);
        $this->db->set('CreatedDate', 'NOW()', FALSE);
		$this->db->insert('payroll', $data);
    }
    
    function updatePayroll()
    {
        $allAndAddition = id_clean($_POST['grossSalary']) + id_clean($_POST['HealthBenifit']) + id_clean($_POST['PhoneAllowance']) + id_clean($_POST['BankCharge']) + id_clean($_POST['AnnualLeave'])  + id_clean($_POST['AnnualBonus']) + id_clean($_POST['OverTime']);
        $allDeduction = id_clean($_POST['SalaryTax']) + id_clean($_POST['Other']) + id_clean($_POST['SalaryAdvance']);
        $netSalary = $allAndAddition - $allDeduction;
        $data = array( 
            'staffID' => id_clean($_POST['StaffID']),
            'PayRollDate' => db_clean($_POST['PayRollDate']),
            'grossSalary' => db_clean($_POST['grossSalary']),
            'SalaryTax' => db_clean($_POST['SalaryTax']),
            'Other' => db_clean($_POST['Other']),
            'SalaryAdvance' => db_clean($_POST['SalaryAdvance']),
            'HealthBenifit' => db_clean($_POST['HealthBenifit']),
            'PhoneAllowance' => db_clean($_POST['PhoneAllowance']),
            'BankCharge' => db_clean($_POST['BankCharge']),
            'AnnualLeave' => db_clean($_POST['AnnualLeave']),
            'AnnualBonus' => db_clean($_POST['AnnualBonus']),
            'OverTime' => db_clean($_POST['OverTime']),
            'NetSalary' => $netSalary,
            'Notes' => db_clean($_POST['Notes'])
		);
        $this->db->set('ModifiedDate', 'NOW()', FALSE);
        $this->db->where('payRollID',id_clean($_POST['payRollID']));
		$this->db->update('payroll', $data);
    }

}
?>